package LogicSide;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sun.istack.internal.FinalArrayList;

import DBside.JDBCConnection;
import DBside.JDBCConnection.Table;
import DBside.TableItem;
import DBside.Tools;

public class AlbumModifier {
	/*
	 * public static void main(String[] args) throws SQLException {
	 * JDBCConnection con = new JDBCConnection();
	 * con.openConnection(Tools.LocalURL, Tools.LocaluserName,
	 * Tools.Localpassword); Session s=new Session(); s.setCon(con);
	 * //System.out.println(removeAlbum("giggapuddi1", s));
	 * //System.out.println("result: "+addNewAlbum("giggapuddi1", s));
	 * 
	 * }
	 */
	// enter album_name to db with new id if not there already, otherwise return
	// the id from the db
	/**
	 * 
	 * @param album_name
	 * @param s
	 * @return id of inserted album, -1 for errors
	 * @throws SQLException
	 */
	public static int addNewAlbum(String album_name, Session s)
			throws SQLException {
		ResultSet album_rs = null;
		int album_id = -1;
		try {
			s.getCon().getConnection().setAutoCommit(false);
			TableItem[] ti = new TableItem[1];
			ti[0] = new TableItem("name", Tools.Type.VARCHAR, album_name);
			album_rs = s.getCon().select("id", ti, null, null,
					JDBCConnection.Table.ALBUM);
			if (Tools.isEmpty(album_rs)) {// not in db. get new id and add
											// TODO thats not good enough
				album_rs = s.getCon().insert(JDBCConnection.Table.ALBUM, ti);
				if (Tools.isEmpty(album_rs)) {
					// TODO problem find creative way to respond
				} else {
					album_rs.next();
					s.getCon().getConnection().commit();

					return album_rs.getInt(1);
				}
			} else {// in db. return it's id.
				album_rs.next();
				s.getCon().getConnection().commit();

				return album_rs.getInt("id");
			}
			s.getCon().getConnection().commit();
		} catch (SQLException e) {
			if (s.getCon() != null)
				s.getCon().getConnection().rollback();
		} finally {
			if (album_rs != null)
				album_rs.close();
		}
		return album_id;
	}
/**
 * 
 * @param album_name
 * @param s
 * @return return id of deleted album, -1 for error
 * @throws SQLException
 */
	public static int removeAlbum(String album_name, Session s)
			throws SQLException {// if in db remove, if not do nothing return id
									// of removed album
		TableItem[] ti = new TableItem[1];
		ResultSet album_rs=null;
		int id = -1;
		try
		{
		s.getCon().getConnection().setAutoCommit(false);
		ti[0] = new TableItem("name", Tools.Type.VARCHAR, album_name);
		album_rs = s.getCon().select("id", ti, null, null,
				JDBCConnection.Table.ALBUM);
		if (!Tools.isEmpty(album_rs)) {
			album_rs.next();
			id = album_rs.getInt("id");
			s.getCon().delete(ti, null, Table.ALBUM);// TODO make sure it works
															// when shai fix
			}
		s.getCon().getConnection().commit();
		}
		catch(SQLException e)
		{
			if (s.getCon() != null)
				s.getCon().getConnection().rollback();
		}
		finally {
			if (album_rs != null)
				album_rs.close();
		}
		return id;
	}
/**
 * 
 * @param album_name
 * @param s
 * @return remove all songs and artists related to album, return all their ids, empty list for errors
 * @throws SQLException
 */
	public static List<Integer> removeArtistAndSongs(String album_name,
			Session s) throws SQLException {// returned list has all ids of
											// removed songs and then id of
											// removed album
		int album_id = removeAlbum(album_name, s);
		ResultSet song_rs=null;
		try
		{
		s.getCon().getConnection().setAutoCommit(false);
		if (album_id != -1) {
			// now get all songs id's and delete them using the deleteSong
			// procedure
			TableItem[] ti = new TableItem[1];
			ti[0] = new TableItem("album_id", Tools.Type.INT, album_id);
			song_rs = s.getCon().select("song_id", ti, null, null,
					JDBCConnection.Table.ALBUMVSSONG);
			List<Integer> removedAlbumAndSongs = new ArrayList<Integer>();
			if (!Tools.isEmpty(song_rs)) {// if empty nothing to do, so take all
											// song_id and delete them
				while (song_rs.next()) {
					removedAlbumAndSongs.add(song_rs.getInt("song_id"));
				}
			}
			// s.getCon().delete(ti, null, Table.ALBUMVSSONG);//removes all
			// songs with that album from albumvssong TODO delete this when elad
			// fixes deleteSong

			for (Integer s_id : removedAlbumAndSongs)
				ManageSongs.deleteSong(s_id, s);

			removedAlbumAndSongs.add(album_id);
			s.getCon().getConnection().commit();

			return removedAlbumAndSongs;

		}
		s.getCon().getConnection().commit();
		}
		catch(SQLException e)
		{
			if (s.getCon() != null)
				s.getCon().getConnection().rollback();
		}
		finally
		{
			if (song_rs != null)
				song_rs.close();	
		}
		return null;
	}
}
